insert overwrite table jms_dm.dm_tab_scan_piece_summary_dt partition(dt)
select format_scan_time,
       scan_user_code,
       organization_code,
       supplier_id,
       scan_type_code,
       network_code,
       organization_name,
       supplier_code,
       supplier_name,
       scan_type,
       network_name,
       network_type,
       pre_site_code,
       pre_site_name,
       next_site_code,
       next_site_name,
       shipment_no,
       shipment_name,
       scan_user,
       scan_user_id,
       scan_user_type,
       parts_cnt,
       packages_cnt,
       in_packages_cnt,
       (parts_cnt + in_packages_cnt) scan_cnt,
       weight,
       hours,
       date(dt) date_time,
       trim(substr(concat(DATE_FORMAT(format_scan_time,'yyyy-MM-dd HH:00-'),from_unixtime (unix_timestamp(DATE_FORMAT(format_scan_time,'yyyy-MM-dd HH:00:00') ,'yyyy-MM-dd HH:mm:ss') ,'HH'),':59'),12,12)),
       table_type,
       bz_type,
       md5(concat(nvl(scan_user_code,''),nvl(format_scan_time,''),nvl(pre_site_code,''),nvl(next_site_code,''),nvl(network_code,''),nvl(shipment_no,''))) one_id,
       dt
from (select format_scan_time,
             scan_user_code,
             organization_code,
             supplier_id,
             scan_data_type scan_type_code,
             network_code,
             organization_name,
             supplier_code,
             supplier_name,
             scan_type,
             network_name,
             network_type,
             pre_site_code,
             pre_site_name,
             next_site_code,
             next_site_name,
             shipment_no,
             shipment_name,
             scan_user,
             scan_user_id,
             scan_user_type,
             table_type,
             bz_type,
             sum(
                     `if`(waybill_no not like 'B%' AND package_code IS NULL, 1, 0)
                 )       parts_cnt,
             sum(
                     `if`(waybill_no like 'B%', 1, 0)
                 )       packages_cnt,
             sum(
                     `if`(waybill_no  not like 'B%' AND package_code IS NOT NULL, 1, 0)
                 )       in_packages_cnt,
             sum(weight) weight,
             hours,
             dt
      from jms_dm.dm_tab_scan_piece_detail_dt
      where dt between date_add('{{ execution_date | cst_ds }}', -2 + 1) and '{{ execution_date | cst_ds }}'
      group by scan_user_code,
               organization_code,
               format_scan_time,
               supplier_id,
               scan_data_type,
               network_code,
               organization_name,
               supplier_code,
               supplier_name,
               scan_type,
               network_name,
               network_type,
               pre_site_code,
               pre_site_name,
               next_site_code,
               next_site_name,
               shipment_no,
               shipment_name,
               scan_user,
               scan_user_id,
               scan_user_type,table_type,bz_type, hours, dt) detail distribute by dt,abs(hash(one_id))%20;
